const connection = require('../app/database')
class RegisterService {
  async registerItem(sportId, athleteId) {
    const statement = `
      INSERT INTO athletes_sports(athlete_id,sport_id) VALUES (?,?);
    `
    const statement2 = `
      INSERT INTO checks(state,sport_id,athlete_id) VALUES (?,?,?)
    `
    await connection.execute(statement, [athleteId, sportId])
    await connection.execute(statement2,[0,sportId,athleteId])
  }
  async getRegisterList(pagenum='1',pagesize='10',sportName = '', athleteName = '',sportId='',athleteId='') {
    const statement = `
      SELECT (SELECT COUNT(*) FROM athletes_sports) totalCount,
      JSON_ARRAYAGG(JSON_OBJECT('id',t.id,'sportName',t.sport_name,'athleteName',t.athlete_name,'time',t.sport_time,'athleteId',t.athlete_id,'sportId',t.sport_id)) list
      FROM (
      SELECT s.sport_name,a.athlete_name,s.sport_time,s.sport_id,a.athlete_id,aths.id id
      FROM athletes_sports aths JOIN sports s 
      ON aths.sport_id = s.sport_id JOIN athletes a 
      ON a.athlete_id = aths.athlete_id
      WHERE s.sport_id LIKE '%${sportId}%' AND a.athlete_id LIKE '%${athleteId}%' AND s.sport_name LIKE '%${sportName}%' AND a.athlete_name LIKE '%${athleteName}%'
      LIMIT ?,?) t;
    `
    const offset = (pagenum - 1) * pagesize
    const [res] = await connection.execute(statement,[offset+'',pagesize])
    return res[0]
   
  }
  async removeRegister(id) {
    const statement = `
      DELETE FROM athletes_sports WHERE id = ?;
    `
    const [res] = await connection.execute(statement,[id])
    return res
  }
  async removeRegisterItem(sportId, athleteId) {
    const statement = `
      DELETE FROM athletes_sports WHERE sport_id = ? AND athlete_id = ?;
    `
    const statement2 = `
      DELETE FROM checks WHERE sport_id = ? AND athlete_id = ?;
    `
    await connection.execute(statement,[sportId, athleteId])
    await connection.execute(statement2,[sportId, athleteId])
  }
  async getRegistedItemsByAthleteId(id) {
    const statement = `
      SELECT 
      JSON_ARRAYAGG(JSON_OBJECT('id',t.id,'sportName',t.sport_name,'athleteName',t.athlete_name,'time',t.sport_time,'athleteId',t.athlete_id,'sportId',t.sport_id)) list
      FROM (
      SELECT s.sport_name,a.athlete_name,s.sport_time,s.sport_id,a.athlete_id,aths.id id
      FROM athletes_sports aths JOIN sports s 
      ON aths.sport_id = s.sport_id JOIN athletes a 
      ON a.athlete_id = aths.athlete_id
      WHERE a.athlete_id = ?) t;
    `
    const [res] = await connection.execute(statement,[id])
    return res[0]
  }
  async getRegistedItemsBySportId(id) {
    const statement = `
      SELECT 
      JSON_ARRAYAGG(JSON_OBJECT('id',t.id,'sportName',t.sport_name,'athleteName',t.athlete_name,'time',t.sport_time,'athleteId',t.athlete_id,'sportId',t.sport_id)) list
      FROM (
      SELECT s.sport_name,a.athlete_name,s.sport_time,s.sport_id,a.athlete_id,aths.id id
      FROM athletes_sports aths JOIN sports s 
      ON aths.sport_id = s.sport_id JOIN athletes a 
      ON a.athlete_id = aths.athlete_id
      WHERE s.sport_id = ?) t;
    `
    const [res] = await connection.execute(statement,[id])
    return res[0]
  }
}

module.exports = new RegisterService()